﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Web.Mvc;
using System.Web.Script.Serialization;

namespace LeEdu.Web.Common
{
    public class SQLBase
    {

        #region MD5加密
        public string MD5JM(string str)
        {
            byte[] result = Encoding.Default.GetBytes(str);    //tbPass为输入密码的文本框
            MD5 md5 = new MD5CryptoServiceProvider();
            byte[] output = md5.ComputeHash(result);
            string md5_pwd = BitConverter.ToString(output).Replace("-", "");  //
            return md5_pwd;
        }

        #endregion



        #region 数据库底层查询方法
        #region DataTable转List
        public List<T> TableToEntity<T>(DataTable dt) where T : class,new()
        {
            // 定义集合
            List<T> ts = new List<T>();

            // 获得此模型的类型
            Type type = typeof(T);
            //定义一个临时变量
            string tempName = string.Empty;
            //遍历DataTable中所有的数据行
            foreach (DataRow dr in dt.Rows)
            {
                T t = new T();
                // 获得此模型的公共属性
                PropertyInfo[] propertys = t.GetType().GetProperties();
                //遍历该对象的所有属性
                foreach (PropertyInfo pi in propertys)
                {
                    tempName = pi.Name;//将属性名称赋值给临时变量
                    //检查DataTable是否包含此列（列名==对象的属性名）  
                    if (dt.Columns.Contains(tempName))
                    {
                        // 判断此属性是否有Setter
                        if (!pi.CanWrite) continue;//该属性不可写，直接跳出
                        //取值
                        object value = dr[tempName];
                        //如果非空，则赋给对象的属性
                        if (value != DBNull.Value)
                            pi.SetValue(t, value, null);
                    }
                }
                //对象添加到泛型集合中
                ts.Add(t);
            }

            return ts;

        }
        #endregion
        #region 通用插入方法
        public int InsertModel<T>(T t) where T : class,new()
        {
            PropertyInfo[] propertys = t.GetType().GetProperties();
            //遍历该对象的所有属性
            string tempName = string.Empty;
            string class_name = t.GetType().Name;
            string _sql = "insert into [" + class_name + "] ";
            string _sql_1 = "";
            string _sql_2 = "";
            int _numid = 0;

            //再用Type.GetProperties获得PropertyInfo[]
            foreach (PropertyInfo pi in propertys)
            {
                if (_numid == 0)
                {
                    _numid++;
                    continue;
                }
                object name = pi.Name;//用pi.GetValue获得值
                // 替换Sql注入符
                string value1 = Convert.ToString(pi.GetValue(t, null)).Replace("'", "''");
                //string dataType = pi.PropertyType.ToString().ToLower();
                string properName = name.ToString().ToLower();
                if (!string.IsNullOrEmpty(value1) && properName != BaseSet.PrimaryKey.ToLower() && properName != BaseSet.TableName.ToLower() && value1 != BaseSet.DateTimeLongNull && value1 != BaseSet.DateTimeShortNull)
                {
                    // 判断是否为空
                    if (value1 == BaseSet.NULL)
                    {
                        value1 = "";
                    }
                    _sql_1 += Convert.ToString(name) + ",";
                    if (pi.ToString().Contains("Int32") || pi.ToString().Contains("Int64"))
                    {
                        _sql_2 += value1 + ",";
                    }
                    else
                    {
                        _sql_2 += "'" + value1 + "',";
                    }

                }
            }


            if (_sql_1.Length > 0)
            {
                _sql_1 = _sql_1.Substring(0, _sql_1.Length - 1);
            } if (_sql_2.Length > 0)
            {
                _sql_2 = _sql_2.Substring(0, _sql_2.Length - 1);
            }
            string _sqlover = _sql + " (" + _sql_1 + ") values(" + _sql_2 + ")";

            return DBExecuteSql(_sqlover);
        }
        #endregion

        #region 通用插入方法 返回主键
        public object GetIDInsertModel<T>(T t) where T : class,new()
        {
            PropertyInfo[] propertys = t.GetType().GetProperties();
            //遍历该对象的所有属性
            string tempName = string.Empty;
            string class_name = t.GetType().Name;
            string _sql = "insert into [" + class_name + "] ";
            string _sql_1 = "";
            string _sql_2 = "";
            int _numid = 0;

            //再用Type.GetProperties获得PropertyInfo[]
            foreach (PropertyInfo pi in propertys)
            {
                if (_numid == 0)
                {
                    _numid++;
                    continue;
                }
                object name = pi.Name;//用pi.GetValue获得值
                // 替换Sql注入符
                string value1 = Convert.ToString(pi.GetValue(t, null)).Replace("'", "''");
                //string dataType = pi.PropertyType.ToString().ToLower();
                string properName = name.ToString().ToLower();
                if (!string.IsNullOrEmpty(value1) && properName != BaseSet.PrimaryKey.ToLower() && properName != BaseSet.TableName.ToLower() && value1 != BaseSet.DateTimeLongNull && value1 != BaseSet.DateTimeShortNull)
                {
                    // 判断是否为空
                    if (value1 == BaseSet.NULL)
                    {
                        value1 = "";
                    }
                    _sql_1 += Convert.ToString(name) + ",";
                    if (pi.ToString().Contains("Int32") || pi.ToString().Contains("Int64"))
                    {
                        _sql_2 += value1 + ",";
                    }
                    else
                    {
                        _sql_2 += "'" + value1 + "',";
                    }

                }
            }


            if (_sql_1.Length > 0)
            {
                _sql_1 = _sql_1.Substring(0, _sql_1.Length - 1);
            } if (_sql_2.Length > 0)
            {
                _sql_2 = _sql_2.Substring(0, _sql_2.Length - 1);
            }
            string _sqlover = _sql + " (" + _sql_1 + ") values(" + _sql_2 + ") SELECT @@identity";
            return DbHelperSQL.GetSingle(_sqlover);


        }
        #endregion
        #region 通用更新方法(指定字段更新)
        public int UpdateModel<T>(T t, string type, List<string> listcol) where T : class,new()
        {
            PropertyInfo[] propertys = t.GetType().GetProperties();
            //遍历该对象的所有属性
            string tempName = string.Empty;
            string class_name = t.GetType().Name;
            string _sql = "update  " + class_name + " set ";
            string _sql_1 = "";//拼接更新字段信息
            string _sql_2 = "";//默认拼接查询条件 按第一个的ID
            int _numid = 0;

            //再用Type.GetProperties获得PropertyInfo[]
            foreach (PropertyInfo pi in propertys)
            {

                object name = pi.Name;//用pi.GetValue获得值
                // 替换Sql注入符
                string value1 = Convert.ToString(pi.GetValue(t, null)).Replace("'", "''");
                //string dataType = pi.PropertyType.ToString().ToLower();
                string properName = name.ToString().ToLower();
                if (_numid == 0)
                {
                    _numid++;
                    //拼接where 条件
                    _sql_2 = " where " + Convert.ToString(name) + "=" + value1;

                    continue;
                }
                if (!string.IsNullOrEmpty(value1) && properName != BaseSet.PrimaryKey.ToLower() && properName != BaseSet.TableName.ToLower() && value1 != BaseSet.DateTimeLongNull && value1 != BaseSet.DateTimeShortNull)
                {

                    // 判断是否为空
                    if (value1 == BaseSet.NULL)
                    {
                        value1 = "";
                    }
                    if (listcol.Any(l => l == Convert.ToString(name)))
                    {
                        if (pi.ToString().Contains("Int32") || pi.ToString().Contains("Int64"))
                        {
                            _sql_1 += Convert.ToString(name) + "=" + value1 + ",";
                        }
                        else
                        {
                            _sql_1 += Convert.ToString(name) + "='" + value1 + "',";
                        }
                    }

                }
            }
            if (_sql_1.Length > 0)
            {
                _sql_1 = _sql_1.Substring(0, _sql_1.Length - 1);
            }
            string _sqlover = _sql + _sql_1 + _sql_2;

            return DBExecuteSql(_sqlover);

        }
        //public int UpdateModel<T>(T t, string type) where T : class,new()
        //{
        //    PropertyInfo[] propertys = t.GetType().GetProperties();
        //    遍历该对象的所有属性
        //    string tempName = string.Empty;
        //    string class_name = t.GetType().Name;
        //    string _sql = "update  " + class_name + " set ";
        //    string _sql_1 = "";//拼接更新字段信息
        //    string _sql_2 = "";//默认拼接查询条件 按第一个的ID
        //    int _numid = 0;

        //    再用Type.GetProperties获得PropertyInfo[]
        //    foreach (PropertyInfo pi in propertys)
        //    {

        //        object name = pi.Name;//用pi.GetValue获得值
        //         替换Sql注入符
        //        string value1 = Convert.ToString(pi.GetValue(t, null)).Replace("'", "''");
        //        string dataType = pi.PropertyType.ToString().ToLower();
        //        string properName = name.ToString().ToLower();
        //        if (_numid == 0)
        //        {
        //            _numid++;
        //            拼接where 条件
        //            _sql_2 = " where " + Convert.ToString(name) + "=" + value1;

        //            continue;
        //        }
        //        if (!string.IsNullOrEmpty(value1) && properName != BaseSet.PrimaryKey.ToLower() && properName != BaseSet.TableName.ToLower() && value1 != BaseSet.DateTimeLongNull && value1 != BaseSet.DateTimeShortNull)
        //        {

        //             判断是否为空
        //            if (value1 == BaseSet.NULL)
        //            {
        //                value1 = "";
        //            }
        //            if (pi.ToString().Contains("Int32") || pi.ToString().Contains("Int64"))
        //            {
        //                _sql_1 += Convert.ToString(name) + "=" + value1 + ",";
        //            }
        //            else
        //            {
        //                _sql_1 += Convert.ToString(name) + "='" + value1 + "',";
        //            }
        //        }
        //    }
        //    if (_sql_1.Length > 0)
        //    {
        //        _sql_1 = _sql_1.Substring(0, _sql_1.Length - 1);
        //    }
        //    string _sqlover = _sql + _sql_1 + _sql_2;

        //    return DBExecuteSql(_sqlover, type);

        //}
        /// <summary>
        /// 通用更新
        /// </summary>
        /// <typeparam name="T">模型</typeparam>
        /// <param name="t">更新对象值</param>
        /// <returns></returns>
        public int Update_Model<T>(T t) where T : class,new()
        {
            PropertyInfo[] propertys = t.GetType().GetProperties();
            //遍历该对象的所有属性
            string tempName = string.Empty;
            string class_name = t.GetType().Name;
            string _sql = "update  [" + class_name + "] set ";
            string _sql_1 = "";//拼接更新字段信息
            string _sql_2 = "";//默认拼接查询条件 按第一个的ID
            int _numid = 0;

            //再用Type.GetProperties获得PropertyInfo[]
            foreach (PropertyInfo pi in propertys)
            {

                object name = pi.Name;//用pi.GetValue获得值
                // 替换Sql注入符
                string value1 = pi.GetValue(t, null).ToString();//当value1为null时不执行，当为""时要执行
                //string dataType = pi.PropertyType.ToString().ToLower();
                string properName = name.ToString().ToLower();
                if (_numid == 0)
                {
                    _numid++;
                    //拼接where 条件
                    _sql_2 = " where " + Convert.ToString(name) + "=" + value1;

                    continue;
                }
                if (value1 != null && properName != BaseSet.PrimaryKey.ToLower() && properName != BaseSet.TableName.ToLower() && value1.ToString() != BaseSet.DateTimeLongNull && value1.ToString() != BaseSet.DateTimeShortNull)
                {

                    // 判断是否为空
                    if (value1.ToString() == BaseSet.NULL)
                    {
                        value1 = "";
                    }
                    if (pi.ToString().Contains("Int32") || pi.ToString().Contains("Int64"))
                    {
                        if (value1.ToString() == "")
                        {
                            value1 = "null";
                        }
                        _sql_1 += Convert.ToString(name) + "=" + value1 + ",";
                    }
                    else
                    {
                        _sql_1 += Convert.ToString(name) + "='" + value1 + "',";
                    }
                }
            }
            if (_sql_1.Length > 0)
            {
                _sql_1 = _sql_1.Substring(0, _sql_1.Length - 1);
            }
            string _sqlover = _sql + _sql_1 + _sql_2;

            return DBExecuteSql(_sqlover);

        }
        #endregion

        #region sql 执行
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql">sql</param>
        /// <returns></returns>
        public int DBExecuteSql(string sql)
        {

            return DbHelperSQL.ExecuteSql(sql);

        }
        #endregion
        #region 集合查询
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tablename"></param>
        /// <param name="ziduan"></param>
        /// <param name="where"></param>

        /// <returns></returns>
        public List<T> GetListModel<T>(string tablename, string ziduan, string where) where T : class,new()
        {
            List<T> ListModel = new List<T>();

            StringBuilder strSql = new StringBuilder();
            if (ziduan == "")
            {
                ziduan = "*";
            }
            strSql.Append("select " + ziduan + " ");
            strSql.Append(" FROM " + tablename + " ");
            if (where.Trim() != "")
            {
                strSql.Append(" where " + where);
            }
            DataSet ds = null;

            ds = DbHelperSQL.Query(strSql.ToString());

            if (ds != null)
            {
                ListModel = TableToEntity<T>(ds.Tables[0]);
            }
            return ListModel;
        }
        #endregion
        #region 获取单个对象
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tablename"></param>
        /// <param name="ziduan"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public T GetModel<T>(string tablename, string ziduan, string where) where T : class,new()
        {
            List<T> ListModel = new List<T>();
            StringBuilder strSql = new StringBuilder();
            if (ziduan == "")
            {
                ziduan = "*";
            }
            strSql.Append("select top(1)" + ziduan + " ");
            strSql.Append(" FROM " + tablename + " ");
            if (where.Trim() != "")
            {
                strSql.Append(" where " + where);
            }
            DataSet ds = null;

            ds = DbHelperSQL.Query(strSql.ToString());
            if (ds != null)
            {
                ListModel = TableToEntity<T>(ds.Tables[0]);
            }

            if (ListModel != null && ListModel.Count > 0)
            {
                return ListModel[0];
            }
            else
            {
                return null;
            }
        }

        #endregion
        #region 分页查询
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="tablename">表名</param>
        /// <param name="ziduan">查询字段</param>
        /// <param name="strWhere"></param>
        /// <param name="orderby">排序</param>
        /// <param name="startIndex"></param>
        /// <param name="endIndex"></param>

        /// <returns></returns>
        public List<T> GetListPageWhere<T>(string tablename, string ziduan, string strWhere, string orderby, int startIndex, int endIndex) where T : class,new()
        {
            List<T> ListModel = new List<T>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT " + ziduan + " FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()) && !orderby.Contains("CHARINDEX"))
            {
                strSql.Append("order by T." + orderby);
            }
            else if (!string.IsNullOrEmpty(orderby.Trim()) && orderby.Contains("CHARINDEX"))
            {
                strSql.Append("order by " + orderby);// CHARINDEX('河北废金属回收',T.gq_title) asc
            }
            else
            {
                // strSql.Append("order by T.gq_id desc");
            }
            strSql.Append(")AS Row, T.*  from " + tablename + " T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            DataSet ds = null;

            ds = DbHelperSQL.Query(strSql.ToString());

            if (ds != null)
            {
                ListModel = TableToEntity<T>(ds.Tables[0]);
            }

            return ListModel;
        }

        public List<T> GetListPageWhere<T>(out int total, string tablename, string ziduan, string strWhere, string orderby, int startIndex, int endIndex) where T : class,new()
        {
            List<T> ListModel = new List<T>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT " + ziduan + " FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()) && !orderby.Contains("CHARINDEX"))
            {
                strSql.Append("order by T." + orderby);
            }
            else if (!string.IsNullOrEmpty(orderby.Trim()) && orderby.Contains("CHARINDEX"))
            {
                strSql.Append("order by " + orderby);// CHARINDEX('河北废金属回收',T.gq_title) asc
            }
            else
            {
                // strSql.Append("order by T.gq_id desc");
            }
            strSql.Append(")AS Row, T.*  from " + tablename + " T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            DataSet ds = null;

            ds = DbHelperSQL.Query(strSql.ToString());

            if (ds != null)
            {
                ListModel = TableToEntity<T>(ds.Tables[0]);
                total = GetListRecordCount(tablename, strWhere);
            }
            else
            {
                total = 0;
            }

            return ListModel;
        }

        #endregion
        #region 获取查询总条数
        /// <summary>
        /// 获取查询总条数
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="strWhere">查询条件</param>

        /// <returns></returns>
        public int GetListRecordCount(string tablename, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM " + tablename + " ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = null;

            obj = DbHelperSQL.GetSingle(strSql.ToString());


            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }


        }
        #endregion
        #endregion

        #region 获取自定义页码分页的算法
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pcount">总页数</param>
        /// <param name="index">当前页</param>
        /// <param name="num">显示几个页</param>
        /// <returns></returns>
        public List<int> GetScodPageParam(int pcount, int index, int num)
        {
            List<int> listp = new List<int>();
            listp.Add(1);//首页
            //上一页
            int temp = index - 1; if (temp == 0) { temp = 1; }
            listp.Add(temp);
            int page_count = num;
            if (pcount < num)//当设定页数超出了总页数
            {
                num = pcount;
                page_count = pcount;
            }
            int p_index = pcount - page_count;
            int f = 0;//设定页奇偶数区分变量
            if (num % 2 == 1)//判断奇偶数
            {
                f = 1;
            }


            for (int i = 1; i <= page_count; i++)
            {
                if (index <= (num / 2 + f))//当前页小于等于设定页的中间页 按1-num 排列
                {
                    listp.Add(i);
                }
                else if ((index > (num / 2 + f)))//当前页大于设定页的中间页 则累加滚动+i
                {
                    //公式： i+当前页-((设定页/2)+f)
                    if ((index + (num / 2 + f)) > pcount)//当前页即将超出总页数时 按最后页排列
                    {
                        listp.Add(i + pcount - 2 * (num / 2 + f) + f);//取最后一组分页的中间数:(总页数-(设定页/2+f)+f)
                    }
                    else
                    {
                        listp.Add(i + index - (num / 2 + f));
                    }
                }
            }

            temp = index + 1; if (temp > pcount) { temp = pcount; }
            //下一页
            listp.Add(temp);
            //尾页
            listp.Add(pcount);//
            return listp;
        }

        #endregion



        #region 集合转json 字符串
        public string ListToJsonString<T>(List<T> list) where T : class,new()
        {
            char[] specialChars = new char[] { ',' };
            string JSONstring = "[";
            T t = new T();
            int index = 0;
            foreach (var item in list)
            {

                JSONstring += "{";
                PropertyInfo[] propertys = item.GetType().GetProperties();
                foreach (var prop in propertys)
                {
                    JSONstring += "\"" + prop.Name + "\":\"" + prop.GetValue(item, null) + "\",";
                }
                JSONstring = JSONstring.TrimEnd(specialChars);
                JSONstring += "},";

                index++;
            }
            JSONstring = JSONstring.TrimEnd(specialChars);
            JSONstring += "]";

            return JSONstring;



        }
        #endregion

        #region JSON字符串 转 集合 对象
        /// <summary>
        /// JSON字符串 转 集合 对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="json"></param>
        /// <returns></returns>
        public List<T> JsonToListModel<T>(string json) where T : class,new()
        {
            string str = json;
            JavaScriptSerializer js = new JavaScriptSerializer();
            List<T> jg = js.Deserialize<List<T>>(str);
            return jg;
        }



        #endregion




        #region 随机数
        //// <summary>
        /// 生成验证码
        /// </summary>
        /// <param name="length">指定验证码的长度</param>
        /// <returns></returns>
        public string CreateValidateCode(int length)
        {
            int[] randMembers = new int[length];
            int[] validateNums = new int[length];
            string validateNumberStr = "";
            //生成起始序列值
            int seekSeek = unchecked((int)DateTime.Now.Ticks);
            Random seekRand = new Random(seekSeek);
            int beginSeek = (int)seekRand.Next(0, Int32.MaxValue - length * 10000);
            int[] seeks = new int[length];
            for (int i = 0; i < length; i++)
            {
                beginSeek += 10000;
                seeks[i] = beginSeek;
            }
            //生成随机数字
            for (int i = 0; i < length; i++)
            {
                Random rand = new Random(seeks[i]);
                int pownum = 1 * (int)Math.Pow(10, length);
                randMembers[i] = rand.Next(pownum, Int32.MaxValue);
            }
            //抽取随机数字
            for (int i = 0; i < length; i++)
            {
                string numStr = randMembers[i].ToString();
                int numLength = numStr.Length;
                Random rand = new Random();
                int numPosition = rand.Next(0, numLength - 1);
                validateNums[i] = Int32.Parse(numStr.Substring(numPosition, 1));
            }
            //生成验证码
            for (int i = 0; i < length; i++)
            {
                validateNumberStr += validateNums[i].ToString();
            }
            return validateNumberStr;
        }
        #endregion
        #region 繁体转简体
        //public string StringConvert(string x, string type)
        //{
        //    String value = String.Empty;
        //    switch (type)
        //    {
        //        case "1"://转繁体
        //            value = Microsoft.VisualBasic.Strings.StrConv(x, Microsoft.VisualBasic.VbStrConv.TraditionalChinese, 0);
        //            break;
        //        case "2":
        //            value = Microsoft.VisualBasic.Strings.StrConv(x, Microsoft.VisualBasic.VbStrConv.SimplifiedChinese, 0);
        //            break;
        //        default:
        //            break;
        //    }
        //    return value;
        //}
        #endregion
        #region 取出特殊字符 除去 数字 空格 英文 中文 字母外
        /// <summary>
        /// The replace.
        /// </summary>
        /// <param name="regex">
        /// The regex.
        /// </param>
        /// <param name="input">
        /// The input.
        /// </param>
        /// <returns>
        /// The <see cref="string"/>.
        /// </returns>
        public  string StringReplaceRegex(Regex regex, string input)
        {
            string inputReplaced = null;

            inputReplaced = regex.Replace(input, " ");

            return inputReplaced;
        }
        #endregion

    }
    public class BaseSet
    {
        public static string NULL
        {
            get { return "@null"; }


        }


        public static string DateTimeShortNull
        {
            get { return "0001-1-1 0:00:00"; }


        }


        public static string DateTimeLongNull
        {
            get { return "0001-01-01 00:00:00"; }


        }


        public static string PrimaryKey
        {
            get { return "PrimaryKey"; }


        }


        public static string TableName
        {
            get { return "TableName"; }


        }
    }
}
